﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SnowEntity;
using System.Data;
using System.Collections;
using Common.Expansion;

namespace SnowDAL
{
    public class UserDAL : BaseDAL
    {
        public DataTable QueryUserInfo(Hashtable where)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(
@"select t.*,p.name,p.`sex`,p.`qq`,p.`province`,p.`city` 
from userinfo t  join `userdetail` p on t.`uid`=p.`uid` where t.`state`<>-1 ");

            foreach (string key in where.Keys)
            {
                string item = where[key].ToString();
                sb.Append(" AND ");
                if (key == "Sex")
                    sb.AppendFormat("p.sex='{0}'", item);
                if (key == "Uid")
                    sb.AppendFormat("t.uid={0}", item);
                if (key == "Name")
                    sb.AppendFormat("p.name like '%{0}%'", item);
                if (key == "LoginName")
                    sb.AppendFormat("t.login_name like '%{0}%'", item);
                if (key == "Wei")
                    sb.Append(" t.`state`<>-0  ");
                if (key == "Date")
                {
                    switch (item)
                    {
                        case "最近3天":
                            sb.Append("t.reg_time >= date_add(now(), interval -3 day)");
                            break;
                        case "最近1周":
                            sb.Append("t.reg_time >= date_add(now(), interval -7 day)");
                            break;
                        case "最近半月":
                            sb.Append("t.reg_time >= date_add(now(), interval -15 day)");
                            break;
                        case "最近1月":
                            sb.Append("t.reg_time >= date_add(now(), interval -30 day)");
                            break;
                        case "最近3月":
                            sb.Append("t.reg_time >= date_add(now(), interval -90 day)");
                            break;
                        default:
                            sb.Append("t.reg_time >= date_add(now(), interval -3 day)");
                            break;
                    }
                }
            }
            sb.Append(" order by t.`uid` desc ");

            return SQLHelper.ExecuteDataSet(sb.ToString()).Tables[0];
        }

        public UserInfo GetUserInfo(string uid)
        {
            string query = "select * from userinfo t where t.uid=" + uid;
            return SQLHelper.ExecuteDataSet(query).Tables[0].Rows[0].ToEntity<UserInfo>();
        }

        public UserDetail GetUserDetail(string uid)
        {
            string query = "select * from userdetail t where t.uid=" + uid;
            return SQLHelper.ExecuteDataSet(query).Tables[0].Rows[0].ToEntity<UserDetail>();
        }

        public UserLike GetUserLike(string uid)
        {
            string query = "select * from userlike t where t.uid=" + uid;
            return SQLHelper.ExecuteDataSet(query).Tables[0].Rows[0].ToEntity<UserLike>();
        }

        public int UpdateUserDetail(string uid, string content, string name)
        {
            string update = string.Format(" update userdetail set content= '{0}',name='{2}' where uid={1}", content, uid, name);
            return SQLHelper.ExecuteNonQuery(update);
        }

        public int UpdateUserPoint(string uid, string point)
        {
            string update = string.Format(" update userinfo set point= '{0}' where uid={1}", point, uid);
            return SQLHelper.ExecuteNonQuery(update);
        }

        public int UpdateUserLikeContent(string uid, string content)
        {
            string update = string.Format(" update userlike set content= '{0}' where uid={1}", content, uid);
            return SQLHelper.ExecuteNonQuery(update);
        }

        public DataTable QueryMyUser()
        {
            string query = "select * from userinfo where state=0";
            return SQLHelper.ExecuteDataSet(query).Tables[0];
        }

        public int UpdateMyUserRegTime(string uid, string date)
        {
            string update = string.Format(
                "update userinfo t set t.`reg_time`='{0}' where t.`uid`={1}", date, uid);
            return SQLHelper.ExecuteNonQuery(update);
        }

        public DataTable QueryCommend(string sex)
        {
            string query = string.Format(
@"select t.`uid`,t.`name`,concat('{0}',t.`face`) as face from userdetail t  join `userrecommend` p on t.`uid`=p.`uid` where p.`state`<>-1 and t.`state`<>-1 and p.`sex`='{1}'", Domain, sex);
            return SQLHelper.ExecuteDataSet(query).Tables[0];
        }

        public int DeleteCommend(string uid)
        {
            string delete = string.Format(" update `userrecommend` set state=-1 where uid={0}", uid);
            return SQLHelper.ExecuteNonQuery(delete);
        }

        public int CreateCommend(string uid, string sex)
        {
            string create = string.Format(@"insert into userrecommend values(null,{0},'{1}',0)", uid, sex);
            return SQLHelper.ExecuteNonQuery(create);
        }

        public DataTable QueryUserFace(string count)
        {
            string sql = "select uid,name,face from userdetail where state<>0 and state<>-1 order by uid desc limit " + count;
                                                                   
            return SQLHelper.ExecuteDataSet(sql).Tables[0];
        }

        public DataTable QueryUserFaceByUid(string uid)
        {
            string sql = "select uid,name,face from userdetail where uid = " + uid;
            return SQLHelper.ExecuteDataSet(sql).Tables[0];
        }

        public int DeleteUserFace(string uid)
        {
            string sql = " update userdetail set face='' where uid=" + uid;
            return SQLHelper.ExecuteNonQuery(sql);
        }
    }
}
